/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package hk.ces.oxfam.patch.OTWTeamCoordinator.importCoordinator;

import hk.ces.oxfam.patch.OTWTeamCoordinator.CompanyItem;
import java.io.InputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.util.List;
import java.util.ArrayList;
import java.sql.SQLException;
import org.apache.poi.hssf.usermodel.HSSFCell;

/**
 *
 * @author ivanlin
 */
public class ImportDummyData
{
  //company
  private static final int ID_CELL = 0;
  private static final int NAME_EN = 1;
  private static final int NAME_CH = 2;

  //team coordinator
  private static final int COOR_ID_CELL = 6;
  private static final int COOR_COMPANY_ID_CELL = 7;
  private static final int COOR_CHINESE_SURNAME_CELL = 10;
  private static final int COOR_CHINESE_NAME_CELL = 11;
  private static final int COOR_DEFAULTED_CELL = 34;
  private static final int COOR_DISABLED_CELL = 35;


  private InputStream companyInputStream = null;
  private InputStream coordinatorInputStream = null;
  
  public static void main(String[] args) throws IOException, SQLException
  {
    //ImportDummyData importDummyData = new ImportDummyData();

    //importDummyData.doImportCompany();
    //importDummyData.doImportCoordinator();
  }

  public ImportDummyData()
  {
    this.companyInputStream = this.getClass().getResourceAsStream("/Company.xls");
    this.coordinatorInputStream = this.getClass().getResourceAsStream("/TeamCoordinator.xls");
  }

  public void doImportCompany() throws IOException, SQLException
  {
    HSSFWorkbook workbook = new HSSFWorkbook(companyInputStream);
    HSSFSheet sheet = workbook.getSheetAt(0);

    System.out.println(sheet.getLastRowNum());

    List<CompanyItem> items = new ArrayList<CompanyItem>();
    for (int rowNo = 1; rowNo <= sheet.getLastRowNum(); rowNo++)
    {
      HSSFRow row = sheet.getRow(rowNo);
      CompanyItem item = new CompanyItem();
      item.setId(Integer.parseInt(row.getCell(ID_CELL).getStringCellValue()));
      item.setNameCH(this.getStringValue(row.getCell(NAME_CH)));
      item.setNameEn(this.getStringValue(row.getCell(NAME_EN)));

      items.add(item);
    }

    new CompanyDAOUtil().insertToDb(items);
  }

  public void doImportCoordinator() throws IOException, SQLException
  {
    HSSFWorkbook workbook = new HSSFWorkbook(coordinatorInputStream);
    HSSFSheet sheet = workbook.getSheetAt(0);

    System.out.println(sheet.getLastRowNum());

    List<TeamCoordinatorItem> items = new ArrayList<TeamCoordinatorItem>();
    for (int rowNo = 1; rowNo <= sheet.getLastRowNum(); rowNo++)
    {
      HSSFRow row = sheet.getRow(rowNo);
      TeamCoordinatorItem item = new TeamCoordinatorItem();
      item.setId(Integer.parseInt(row.getCell(COOR_ID_CELL).getStringCellValue()));
      item.setCompanyId(this.getIntValue(row.getCell(COOR_COMPANY_ID_CELL)));
      item.setChineseSurname(this.getStringValue(row.getCell(COOR_CHINESE_SURNAME_CELL)));
      item.setChineseName(this.getStringValue(row.getCell(COOR_CHINESE_NAME_CELL)));
      item.setDefaulted(this.getBooleanValue(row.getCell(COOR_DEFAULTED_CELL)));
      item.setDisabled(this.getBooleanValue(row.getCell(COOR_DISABLED_CELL)));

      items.add(item);
    }

    new TeamCoordinatorDAOUtil().insertToDb(items);
  }

  private boolean getBooleanValue(HSSFCell cell)
  {
    if (cell == null)
    {
      return false;
    }

    return Integer.parseInt(cell.getStringCellValue()) == 0 ? false : true;
  }

  private int getIntValue(HSSFCell cell)
  {
    return cell == null ? 0 : Integer.parseInt(cell.getStringCellValue());
  }

  private String getStringValue(HSSFCell cell)
  {
    return cell == null ? "" : cell.getStringCellValue().trim();
  }

  
}
